package databaseDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import sun.security.util.Length;
import bean.RoleBean;


public class RoleManagement {

	public List<RoleBean> queryRoles() throws Exception {
		List<RoleBean> list = new ArrayList<RoleBean>();
		Connection con = DataAccess.getConnection();
		String sql = "select * from roles where roleID != '1'";

		ResultSet rs = null;
		RoleBean rb = null;
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				rb = new RoleBean();
				rb.setRoleID(rs.getString(1));
				rb.setRoleName(rs.getString(2));
				rb.setNickName(rs.getString(3));
				rb.setRoleDescription(rs.getString(4));
				rb.setMenuIDs(new Menu2Dao().getMenuIDByroleID(rb.getRoleID()));
				list.add(rb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public RoleBean queryRolesByRoid(String roleID) throws Exception {
		
		Connection con = DataAccess.getConnection();
		String sql = "select * from roles where roleID = '"+roleID+"'";

		ResultSet rs = null;
		RoleBean rb = null;
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				rb = new RoleBean();
				rb.setRoleID(rs.getString(1));
				rb.setRoleName(rs.getString(2));
				rb.setNickName(rs.getString(3));
				rb.setRoleDescription(rs.getString(4));
				rb.setMenuIDs(new Menu2Dao().getMenuIDByroleID(rb.getRoleID()));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return rb;
	}
	
	
	

	public int insertNewRole(RoleBean rb) throws Exception {
		Connection con = DataAccess.getConnection();  // 获取连接
		String sql = "insert into roles values(?,?,?,?)";  // 编写sql语句
		PreparedStatement pst = null; // 创建预处理语句
		try{
			pst = con.prepareStatement(sql);  // 拿到预处理
			pst.setString(1, rb.getRoleID());
			pst.setString(2, rb.getRoleName());
			pst.setString(3, rb.getNickName());
			pst.setString(4, rb.getRoleDescription());
			
			//pst.setString(5, ub.getRoleName());
			pst.executeUpdate(); // 提交
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}finally{
			try {
				if(pst!=null){
					pst.close();
					
				}
				if(con!=null){
					con.close();
					
				}
			} catch (Exception e2) {
				e2.printStackTrace();
				return 0;
			}
		}
		return 1;
		
	}

	public void delRoleByID(String roleID) throws ClassNotFoundException, SQLException {
		
		Connection con = DataAccess.getConnection();
		//  
		String sql = "delete from roles where roleID=?";
		PreparedStatement pst = null;
		
		
		pst = con.prepareStatement(sql);
		pst.setString(1, roleID);
		pst.executeUpdate();
		
			try{
				
				if(pst!=null){
					pst.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		
	}
	
	
	
}
